package bysj.dao;

import bysj.domain.Student;
import bysj.domain.StudentClass;
import bysj.domain.Teacher;
import bysj.service.StudentClassService;
import bysj.service.TeacherService;
import util.Condition;
import util.JdbcHelper;
import util.Pagination;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Collection;
import java.util.HashSet;
import java.util.List;

public class StudentDao {
    private static StudentDao studentDao =null;

    private StudentDao() {
    }

    public static StudentDao getInstance() {
        if(studentDao ==null){
            studentDao = new StudentDao();
        }
        return studentDao;
    }
    public Collection<Student> findAll(Connection connection, List<Condition> conditionList, Pagination pagination) throws SQLException {
        //创建集合类对象，用来保存所有记录代表的Department对象
        Collection<Student> students = new HashSet<>();
        StringBuilder sql_select = new StringBuilder("SELECT * FROM student ");
        int totalNum = CommonDao.getInstance().count(connection,"student");
        //如果有条件，则生成条件子句，并附加到主语句后面
        if(conditionList!=null){
            String whereClause = Condition.toWhereClause(conditionList);
            sql_select.append(whereClause);
        }
        if(pagination!=null){
            String limit= pagination.toLimitClause(totalNum)+" ";
            sql_select.append(limit);
        }
        System.out.println(sql_select.toString());
        PreparedStatement preparedStatement = connection.prepareStatement(sql_select.toString());
        //执行预编译语句，结果集保存在resultSet中
        ResultSet resultSet = preparedStatement.executeQuery();
        while (resultSet.next()) {
            int id = resultSet.getInt("id");
            String name = resultSet.getString("name");
            String no = resultSet.getString("no");
            String remarks = resultSet.getString("remarks");
            int studentClass_id = resultSet.getInt("studentClass_id");
            StudentClass studentClass = StudentClassService.getInstance().find(studentClass_id);
            // Teacher supervisor =
            Student student = new Student(id,name,no,remarks,studentClass);
            //将school增加到schools集合
            students.add(student);
        }
        //关闭三个资源
        JdbcHelper.close(resultSet, preparedStatement);
        return students;
    }
    public Collection<Student> findAll(Connection connection, Teacher teacher, Pagination pagination) throws SQLException {
        //创建集合类对象，用来保存所有记录代表的Department对象
        Collection<Student> students = new HashSet<>();
        StringBuilder sql_select = new StringBuilder("SELECT * FROM student where teacher_id = "+teacher.getId());
        int totalNum = CommonDao.getInstance().count(connection,"student");
        //如果有条件，则生成条件子句，并附加到主语句后面
        /*if(conditionList!=null){
            String whereClause = Condition.toWhereClause(conditionList);
            sql_select.append(whereClause);
        }*/
        if(pagination!=null){
            String limit= pagination.toLimitClause(totalNum)+" ";
            sql_select.append(limit);
        }
        System.out.println(sql_select.toString());
        PreparedStatement preparedStatement = connection.prepareStatement(sql_select.toString());
        //执行预编译语句，结果集保存在resultSet中
        ResultSet resultSet = preparedStatement.executeQuery();
        while (resultSet.next()) {
            int id = resultSet.getInt("id");
            String name = resultSet.getString("name");
            String no = resultSet.getString("no");
            String remarks = resultSet.getString("remarks");
            int studentClass_id = resultSet.getInt("studentClass_id");
            StudentClass studentClass = StudentClassService.getInstance().find(studentClass_id);
            Student student = new Student(id,name,no,remarks,studentClass,teacher);
            //将school增加到schools集合
            students.add(student);
        }
        //关闭三个资源
        JdbcHelper.close(resultSet, preparedStatement);
        return students;
    }

    public static Student find(int id,Connection connection) throws SQLException {
        Student student = null;
        //创建sql语句
        String school_sql = "SELECT * FROM student WHERE id = ?";
        //创建预编译对象
        PreparedStatement pstmt= connection.prepareStatement(school_sql);
        //为预编译对象赋值
        pstmt.setInt(1,id);
        //获得结果集对象
        ResultSet resultSet = pstmt.executeQuery();
        if (resultSet.next()){
           // int id = resultSet.getInt("id");
            String name = resultSet.getString("name");
            String no = resultSet.getString("no");
            int studentClass_id = resultSet.getInt("studentClass_id");
            StudentClass studentClass = StudentClassService.getInstance().find(studentClass_id);
            int teacher_id = resultSet.getInt("teacher_id");
            Teacher teacher = TeacherService.getInstance().find(teacher_id);
            student = new Student(id,name,no,studentClass,teacher);
        }
        //关闭三个资源
        JdbcHelper.close(resultSet, pstmt);
        return student;
    }
    public boolean update(Student student, Connection connection) throws SQLException {
        //准备预编译语句
        String student_sql = "update student set name=?,no=?,remarks=?,studentClass_id=?,teacher_id=? where id="+student.getId();
        //创建预编译对象
        PreparedStatement preparedStatement = connection.prepareStatement(student_sql);
        //为预编译对象赋值
        preparedStatement.setString(1, student.getName());
        preparedStatement.setString(2, student.getNo());
        preparedStatement.setString(3, student.getRemarks());
        preparedStatement.setInt(4,student.getStudentClass().getId());
        preparedStatement.setInt(5,student.getSupervisor().getId());
        //执行预编译对象的executeUpdate方法，获得修改的记录行数
        int rowAdd = preparedStatement.executeUpdate();
        JdbcHelper.close(preparedStatement);
        return rowAdd > 0;
    }
    public boolean add(Student student,Connection connection) throws SQLException {
        //创建预编译对象
        PreparedStatement preparedStatement = connection.prepareStatement("Insert into student(name,no,remarks,studentClass_id,actor_id,teacher_id,id) Values " + "(?,?,?,?,?,?,?)");
        //为预编译对象赋值
        preparedStatement.setString(1, student.getName());
        preparedStatement.setString(2, student.getNo());
        preparedStatement.setString(3, student.getRemarks());
        preparedStatement.setInt(4,student.getStudentClass().getId());
        preparedStatement.setInt(5,student.getId());
        preparedStatement.setInt(6,student.getSupervisor().getId());
        preparedStatement.setInt(7,student.getId());
        //执行预编译对象的executeUpdate方法，获得增加的记录行数
        int rowAdd = preparedStatement.executeUpdate();
        //关闭两个资源
        JdbcHelper.close(preparedStatement);
        return rowAdd > 0;
    }

    public boolean delete(Integer id,Connection connection) throws SQLException {
        //创建sql语句
        String student_sql = "delete from student where id=?";
        //在该连接创建预编译语句对象
        PreparedStatement pstmt = connection.prepareStatement(student_sql);
        //为预编译参数赋值
        pstmt.setInt(1, id);
        //执行预编译对象的executeUpdate方法，获得删除的记录行数
        int affectedRowNum = pstmt.executeUpdate();
        //关闭两个资源
        JdbcHelper.close(pstmt);
        return affectedRowNum > 0;
    }
}
